package com.chinaunicom.research.bigdatalabs.util;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.log4j.Logger;

import java.io.Reader;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;



/**
 * common use
 * Created by zhangxr103 on 2014/11/15.
 */
public class ConnDB {

    private static final String DEFAULT_CONN = "jdbc:mysql://10.2.167.91:3306/creditdb";
    private static final Logger LOG = Logger.getLogger(ConnDB.class);
    private static final int DEFAULT_RETRY_NUM = 10;
    private static ConnDB db;
    Connection conn = null;
    private String USER = "root";
    private String PASSWD = "!@#$QWER";

    private ConnDB() {
        initConnection(DEFAULT_CONN, USER, PASSWD);
    }

    public static ConnDB instance() {
        try {
            if (db == null || db.getConn() == null || db.getConn().isClosed()) {
                db = new ConnDB();
            }
        } catch (SQLException e) {
            db = new ConnDB();
        }
        return db;
    }

    private void initConnection(String url, String user, String passwd) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(url, user, passwd);
            if (!conn.isClosed()) {
                LOG.info("connect to db successed");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private Connection getConn() {
        return this.conn;
    }

    public ResultSet query(String sql) {
        return query(sql,DEFAULT_RETRY_NUM);
    }

    public ResultSet query(String sql,int retryTimes) {
        ResultSet rs = null;
        int retry=0;
        while(retry++<retryTimes){
            try {
                if (conn != null) {
                    Statement stmt = conn.createStatement();
                    rs = stmt.executeQuery(sql);
                    LOG.info("execute query:" + sql);
                    break;
                }else{
                    throw new RuntimeException("DB connection is closed");
                }
            } catch (SQLException e) {
                LOG.error("retry query"+ retry+"times"+e);
                try {
                    Thread.sleep((2^retry));
                } catch (InterruptedException e1) {
                    //do nothing
                }
            }
        }
        return rs;
    }


    public int insert_table(String tableName, String key, String value) throws SQLException {
        TreeMap<String, String> map = Maps.newTreeMap();
        map.put(key, value);
        return insert_table(tableName, map);
    }

    public int insert_table(String tableName, Map<String, String> map) throws SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append("insert into " + tableName + " ");
        StringBuilder value = new StringBuilder("( ");
        StringBuilder colum = new StringBuilder(" ( ");
        boolean first = true;
        for (Map.Entry<String, String> e : map.entrySet()) {
            if (!first) {
                colum.append(",");
                value.append(",");
            } else {
                first = false;
            }
            colum.append(e.getKey());
            value.append(" ? ");
        }
        colum.append(") ");
        value.append(") ");
        sql.append(colum.toString()).append(" values").append(value.toString());
        ConnDB db = ConnDB.instance();
        return db.executeUpdate(sql.toString(), new ArrayList<Object>(map.values()));
    }


    public int update_table(String tableName, String key, Object value, String where) throws SQLException {
        TreeMap<String, Object> map = Maps.newTreeMap();
        map.put(key, value);
        return update_table(tableName, map, where);
    }

    public int update_table(String tableName, Map<String, Object> map, String where) throws SQLException {
        StringBuilder sql = new StringBuilder();
        sql.append("update " + tableName + " set ");
        boolean first = true;
        for (Map.Entry<String, Object> e : map.entrySet()) {
            if (!first) {
                sql.append(",");
            } else {
                first = false;
            }
            sql.append(e.getKey() + " = ?");
        }
        if (Tools.notNullOrEmpty(where)) {
            sql.append(" where " + where);
        }
        List<Object> value = Lists.newArrayList(map.values());
        return executeUpdate(sql.toString(), value);
    }

    public int delete_table(String tableName, String where) throws SQLException {
        ConnDB db = ConnDB.instance();
        String sql = "delete from " + tableName + " where " + where;
        LOG.info("execute delete: :" + sql);
        int result = 0;
        if (conn != null) {
            Statement stmt = conn.createStatement();
            return stmt.executeUpdate(sql);
        }
        return 0;
    }


    private int executeUpdate(String sql, List<Object> params) throws SQLException {
        int retry=0;
        while (true){
            try {
                return innerUpdate(sql,params);
            } catch (SQLException e) {
                if(retry++<DEFAULT_RETRY_NUM){
                    throw e;
                }
                try {
                    Thread.sleep((2^retry));
                } catch (InterruptedException e1) {
                    //do nothing
                }
                LOG.error("will retry update:"+retry+" time"+e);
            }
        }
    }

    private int innerUpdate(String sql, List<Object> params) throws SQLException {
        int result = 0;
        if (conn != null) {
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            ParameterMetaData meta = stmt.getParameterMetaData();
            for (int i = 0; i < params.size(); i++) {
                Object o = params.get(i);
                if(o instanceof Reader){
                    stmt.setCharacterStream(i + 1, (Reader) o);
                }else
                if (o instanceof Integer) {
                    stmt.setInt(i + 1, (Integer) o);
                } else {
                    stmt.setString(i + 1, params.get(i).toString());
                }
            }
            result = stmt.executeUpdate();
        }
        return result;
    }

    public void close() {
        try {
            if (conn != null)
                conn.close();
            conn = null;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        db = null;
    }


}
